﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using University.Workshop.Model;
using System.Data.SqlClient;
using System.Data;

namespace University.Workshop.DataAccess
{
    public static class ReportSalesProduct
    {
        public const string ConnString = "Data Source=.\\sqlexpress; Initial Catalog=Pharmacy;Integrated Security=true";


        public static DataTable GetReport(DateTime dateInit, DateTime dateEnd, string client, string product)
        {
            string sqlSentence = "select  p.Code As 'Codigo de Prod.',p.Name As 'Nombre de Prod.',SUM(sd.Quantity) Cantidad from Product p, Sale s, SaleDetail sd where p.Code=sd.CodProduct And sd.CodeSale=s.Code And s.DateSale>@dateInit And s.DateSale<@dateEnd And s.CodeClient like @client And sd.CodProduct like @product group by p.Code , p.Name";
            SqlConnection cnn = null;
            SqlConnection tempCnn = null;
            DataTable resp = new DataTable() { Locale = System.Globalization.CultureInfo.CurrentCulture }; 

            try
            {
                tempCnn = new SqlConnection(ConnString);
                cnn = tempCnn;
                cnn.Open();
                SqlCommand cmd = tempCnn.CreateCommand();
                cmd.CommandText = sqlSentence;
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.Parameters.AddWithValue("@dateEnd", dateEnd);
                cmd.Parameters.AddWithValue("@dateInit", dateInit);
                cmd.Parameters.AddWithValue("@client", '%' + client + '%');
                cmd.Parameters.AddWithValue("@product", '%' + product + '%');
                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                resp.Load(reader);
                return resp;

            }
            /*catch (Exception e)
            {
                throw new DataAccessException("Error creating a user", e);
            }*/
            finally
            {
                if (tempCnn != null)
                    tempCnn.Close();
                if (resp != null)
                {
                    resp.Dispose();
                }
            }
        }
    }
}
